/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.seari.utils;

import com.seari.bean.DeviceList;
import com.seari.bean.DeviceType;
import com.seari.bean.DeviceTypeAttr;
import com.seari.utils.excel.ImportExcelUtil;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.CollectionUtils;

/**
 *
 * @author Rainbow
 */
public class IEC104DeviceListHelper
{

    public String typePrefix = "moon";
    public String typeSuffix = ".sta.light";
    public Set<String> typeSet = new HashSet<>();
    public List<String> typeList = new ArrayList<>();
    public int driverStart = 51;
    public int driverDataCount = 5000;
    public String fileName = "moon104";
    public int gatewayStartNum = 1;
    public static void main(String[] args) throws Exception
    {
        List<List<Object>> valList = new ImportExcelUtil().getBankListByExcel(new FileInputStream(new File("C:\\Users\\Rainbow\\Desktop\\moon.xlsx")), "SUN2.xlsx");
        IEC104DeviceListHelper iec = new IEC104DeviceListHelper();
        for (List<Object> list : valList)
        {
            if (!iec.typeSet.contains(list.get(2).toString()))
            {
                iec.typeSet.add(list.get(2).toString());
                iec.typeList.add(list.get(2).toString());
            }
        }
        Workbook wb = iec.generateWorkBook();
        wb.write(new FileOutputStream("d:/" + iec.fileName + ".xlsx"));
        wb.close();
    }

    public Workbook generateWorkBook()
    {
        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet();
        int rowNum = generateDeviceType(sheet);
        rowNum = generateDistributionInfo(rowNum, sheet);
        rowNum = generatePeriphAddrMain(rowNum, sheet);
        return wb;
    }

    public int generateDeviceType(Sheet sheet)
    {
        int currentRow = 1;
        Row row = sheet.createRow(0);
        row.createCell(0).setCellValue("# ascii dump of database");
        sheet.createRow(1);//空行
        return currentRow;
    }

    public int generateDistributionInfo(int rowNum, Sheet sheet)
    {
        //生成空白行
        sheet.createRow(rowNum);
        rowNum++;
        //表头
        sheet.createRow(rowNum).createCell(0).setCellValue("# DistributionInfo");
        rowNum++;
        Row newRow = sheet.createRow(rowNum);
        newRow.createCell(0).setCellValue("Manager/User");
        newRow.createCell(1).setCellValue("ElementName");
        newRow.createCell(2).setCellValue("TypeName");
        newRow.createCell(3).setCellValue("_distrib.._type");
        newRow.createCell(4).setCellValue("_distrib.._driver");
        rowNum++;
        //表体
        for (String type : typeList)
        {
            for (int i = 0; i < (driverDataCount * 2); i++)
            {
                Row tableRow = sheet.createRow(rowNum);
                tableRow.createCell(0).setCellValue("ASC (1)/0");
                String newType = type.replace("moon", "");
                int typeInt = Integer.parseInt(newType);
                tableRow.createCell(1).setCellValue(typePrefix + (1000000 + typeInt * 10000 + i) + typeSuffix);
                tableRow.createCell(2).setCellValue(type);
                tableRow.createCell(3).setCellValue(56); //_distrib.._type
                tableRow.createCell(4).setCellValue("\\" + driverStart); //_distrib.._driver
                rowNum++;
                if (i == driverDataCount-1)
                {
                    driverStart++;
                }
            }
            driverStart++;
        }

        return rowNum;
    }

    public int generatePeriphAddrMain(int rowNum, Sheet sheet)
    {
        //生成空白行
        sheet.createRow(rowNum);
        rowNum++;
        //表头
        sheet.createRow(rowNum).createCell(0).setCellValue("# PeriphAddrMain");
        rowNum++;
        Row newRow = sheet.createRow(rowNum);
        newRow.createCell(0).setCellValue("Manager/User");
        newRow.createCell(1).setCellValue("ElementName");
        newRow.createCell(2).setCellValue("TypeName");
        newRow.createCell(3).setCellValue("_address.._type");
        newRow.createCell(4).setCellValue("_address.._reference");
        newRow.createCell(5).setCellValue("_address.._poll_group");
        newRow.createCell(6).setCellValue("_address.._connection");
        newRow.createCell(7).setCellValue("_address.._offset");
        newRow.createCell(8).setCellValue("_address.._subindex");
        newRow.createCell(9).setCellValue("_address.._direction");
        newRow.createCell(10).setCellValue("_address.._internal");
        newRow.createCell(11).setCellValue("_address.._lowlevel");
        newRow.createCell(12).setCellValue("_address.._active");
        newRow.createCell(13).setCellValue("_address.._start");
        newRow.createCell(14).setCellValue("_address.._interval");
        newRow.createCell(15).setCellValue("_address.._reply");
        newRow.createCell(16).setCellValue("_address.._datatype");
        newRow.createCell(17).setCellValue("_address.._drv_ident");
        rowNum++;
        //表体
        int val = 0;
        for (String type : typeList)
        {
            int p = 0;
            for (int i = 0; i < (driverDataCount * 2); i++)
            {
                Row tableRow = sheet.createRow(rowNum);
                tableRow.createCell(0).setCellValue("ASC (1)/0");
                String newType = type.replace("moon", "");
                int typeInt = Integer.parseInt(newType);
                tableRow.createCell(1).setCellValue(typePrefix + (1000000 + typeInt * 10000 + i) + typeSuffix);
                tableRow.createCell(2).setCellValue(type);
                tableRow.createCell(3).setCellValue(16); //_address.._type
                if(i != 0 && i%256 == 0)
                {
                    p++;
                    val = 0;
                }
                tableRow.createCell(4).setCellValue("Gateway" + gatewayStartNum + "-11.0.1.0." + p + "." + val); //_address.._reference
                tableRow.createCell(5); //_address.._poll_group
                tableRow.createCell(6); //_address.._connection
                tableRow.createCell(7).setCellValue(0); //_address.._offset
                tableRow.createCell(8).setCellValue(0); //_address.._subindex
                tableRow.createCell(9).setCellValue("\\2"); //_address.._direction
                tableRow.createCell(10).setCellValue(0); //_address.._internal
                tableRow.createCell(11).setCellValue(0); //_address.._lowlevel
                tableRow.createCell(12).setCellValue(1); //_address.._active
                tableRow.createCell(13).setCellValue("01.01.1970 00:00:00.000"); //_address.._start
                tableRow.createCell(14).setCellValue("01.01.1970 00:00:00.000"); //_address.._interval
                tableRow.createCell(15).setCellValue("01.01.1970 00:00:00.000"); //_address.._reply
                tableRow.createCell(16).setCellValue(525); //_address.._datatype
                tableRow.createCell(17).setCellValue("IEC"); //_address.._drv_ident
                rowNum++;

                if(i == driverDataCount-1)
                {
                    gatewayStartNum++;
                    p=0;
                    val = 0;
                }
                val++;
            }
            gatewayStartNum++;
        }
        return rowNum;
    }

}
